﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;

namespace CommunitySystem
{
    public class JuZhuanAnZhiBO
    {
        #region 安置计划
        public ObservableCollection<JuZhuanAnZhiJiHuaModel> GetZhaoLuJiHuaModel(int zhaoluid, string islocked)
        {
            ObservableCollection<JuZhuanAnZhiJiHuaModel> rights = new ObservableCollection<JuZhuanAnZhiJiHuaModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY id ) Sec,* from JuZhuanAnZhiJiHua  where 1=1 ");
            if (zhaoluid != 0)
            {
                sql += " and ID = " + zhaoluid;
            }
            if (!string.IsNullOrEmpty(islocked))
            {
                sql += " and IsLocked = '" + islocked + "'";
            }
            sql += " order by id desc";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(JuZhuanAnZhiJiHuaModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 用于招录职位的条件上的招录计划名称combobox
        /// </summary>
        /// <param name="zhaoluid"></param>
        /// <param name="islocked"></param>
        /// <returns></returns>
        public ObservableCollection<JuZhuanAnZhiJiHuaModel> GetZhaoLuJiHuaModelForCom()
        {
            ObservableCollection<JuZhuanAnZhiJiHuaModel> rights = new ObservableCollection<JuZhuanAnZhiJiHuaModel>();
            string sql = string.Format(@"select * from JuZhuanAnZhiJiHua where islocked = 0
union all
select * from JuZhuanAnZhiJiHua where islocked = 1 order by id desc");
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(JuZhuanAnZhiJiHuaModel.FillEntity(dr));
            }
            return rights;
        }

        public int InsertJuZhuanAnZhiJiHuaTitle(JuZhuanAnZhiJiHuaModel ZhaoLuJiHua)
        {
            string sql = "";
            if (ZhaoLuJiHua != null)
            {
                if (ZhaoLuJiHua.ID == 0)
                    sql = string.Format(@"INSERT INTO JuZhuanAnZhiJiHua
           ([Year]
           ,[Name]
           ,[IsLocked])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}')  ;select @@IDENTITY;", ZhaoLuJiHua.Year, ZhaoLuJiHua.Name, ZhaoLuJiHua.IsLocked);
                else
                    sql = string.Format("update JuZhuanAnZhiJiHua set Name = '{0}' where id = {1};select 0 from JuZhuanAnZhiJiHua", ZhaoLuJiHua.Name,
                                        ZhaoLuJiHua.ID);
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            else return 0;
        }

        public bool DeleteJuZhuanAnZhiJiHua(int id)
        {
            string sql = string.Format(@"delete from JuZhuanAnZhiJiHua where id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool SuoJuZhuanAnZhiJiHua()
        {
            //            string sql = @"update dbo.ZhaoLuZhiWei set islocked = 1 where islocked = 0;
            //update ZhaoLuJiHua set IsLocked = 1 where ID in
            //(select ZhaoLuJiHuaID from ZhaoLuJiHuaDetail1 where ZhaoLuZhiWeiID in
            //(select ID from ZhaoLuZhiWei where IsLocked = 0)) ";
            string sql = @"update JuZhuanAnZhiJiHua set IsLocked = 1 where islocked = 0";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion
        #region 安置人员
        public ObservableCollection<JuZhuanAnZhiJiHuaEmpModel> GetJuZhuanAnZhiJiHuaEmpModel(int zhaoluid, string name, int id)
        {
            ObservableCollection<JuZhuanAnZhiJiHuaEmpModel> rights = new ObservableCollection<JuZhuanAnZhiJiHuaEmpModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY j.id ) Sec,j.[ID]
      ,j.[JiHuaID]
      ,j.[Name]
      ,j.[CardNo]
      ,j.[Gender]
      ,j.[Birthday]
      ,j.[XueLi]
      ,j.[ZhengZhiMianMao],j.AnZhiDanWei
      ,j.[YuanDanWeiZhiWu]
      ,CASE WHEN j.[AnZhiDanWei] = 0 THEN j.[QiTaDanWei] ELSE o.name END QiTaDanWei
      ,j.[ZhiWuCengCi]
      ,j.[ZhiWuShuXing]
      ,j.[Status]
      ,j.[AnZhiShiJian],o.Name OrgName from JuZhuanAnZhiJiHuaEmp j
INNER JOIN dbo.JuZhuanAnZhiJiHua jh ON jh.ID = j.JiHuaID
 LEFT JOIN dbo.OrgForHR o ON j.AnZhiDanWei = o.id  where 1=1 ");
            if (zhaoluid != 0 && zhaoluid != -1)
            {
                sql += " and jh.ID = " + zhaoluid;
            }
            if (id != 0)
            {
                sql += " and j.ID = " + id;
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and j.name like '%" + name + "%'";
            }
            sql += " order by j.id desc";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(JuZhuanAnZhiJiHuaEmpModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddJuZhuanAnZhiJiHuaEmpModel(JuZhuanAnZhiJiHuaEmpModel item)
        {
            string sql = string.Format(@"INSERT INTO JuZhuanAnZhiJiHuaEmp
           ([JiHuaID]
           ,[Name]
           ,[CardNo]
           ,[Gender]
           ,[Birthday]
           ,[XueLi]
           ,[ZhengZhiMianMao]
           ,[YuanDanWeiZhiWu]
           ,[AnZhiDanWei]
           ,[QiTaDanWei]
           ,[ZhiWuCengCi]
           ,[ZhiWuShuXing]
           ,[Status]
           ,[AnZhiShiJian])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}','{13}')", item.JiHuaID, item.Name, item.CardNo, item.Gender
                   , item.Birthday, item.XueLi, item.ZhengZhiMianMao, item.YuanDanWeiZhiWu
                   , item.AnZhiDanWei
                   , item.QiTaDanWei, item.ZhiWuCengCi, item.ZhiWuShuXing, item.Status, item.AnZhiShiJian);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateJuZhuanAnZhiJiHuaEmpModel(JuZhuanAnZhiJiHuaEmpModel item)
        {
            string sql = string.Format(@"UPDATE JuZhuanAnZhiJiHuaEmp
   SET [JiHuaID] = {0}
      ,[Name] = '{1}'
      ,[CardNo] = '{2}'
      ,[Gender] = '{3}'
      ,[Birthday] = '{4}'
      ,[XueLi] = '{5}'
      ,[ZhengZhiMianMao] = '{6}'
      ,[YuanDanWeiZhiWu] = '{7}'
      ,[AnZhiDanWei] = {8}
      ,[QiTaDanWei] = '{9}'
      ,[ZhiWuCengCi] = '{10}'
      ,[ZhiWuShuXing] = '{11}'
      ,[Status] = {12}
      ,[AnZhiShiJian] = '{13}' where id = {14}", item.JiHuaID, item.Name, item.CardNo, item.Gender
                   , item.Birthday, item.XueLi, item.ZhengZhiMianMao, item.YuanDanWeiZhiWu
                   , item.AnZhiDanWei
                   , item.QiTaDanWei, item.ZhiWuCengCi, item.ZhiWuShuXing, item.Status, item.AnZhiShiJian, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeleteJuZhuanAnZhiJiHuaEmp(int id)
        {
            string sql = string.Format(@"delete from JuZhuanAnZhiJiHuaEmp where id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool QueRenJuZhuanAnZhiJiHuaEmpModel(ObservableCollection<JuZhuanAnZhiJiHuaEmpModel> item, DateTime? dt)
        {
            foreach (JuZhuanAnZhiJiHuaEmpModel i in item)
            {
                if (i.AnZhiDanWei != 0)
                {
                    string sql = string.Format("UPDATE dbo.JuZhuanAnZhiJiHuaEmp SET Status = 1,AnZhiShiJian = '{0}' WHERE ID = {1}", dt, i.ID);
                    sql += string.Format(@";INSERT INTO dbo.Emp_BasicInfo (name,CardNo,Gender,Birthday,Political,OrgID,NowWorkDate,HuoDeFangShi,EmpStatus)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');select @@IDENTITY", i.Name, i.CardNo, i.Gender, i.Birthday, i.ZhengZhiMianMao, i.AnZhiDanWei, i.AnZhiShiJian, "军转安置", "在职");
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        int id = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                        string sql1 = string.Format(@"INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,DutyLevel,DutyAttribute,OKDate,JobStatus,JobDate,RXRZWSXSJ)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}' ,'{6}' 
          );insert into Emp_Resume (EmpbasicID,BeginDate,Unit,Duty) values ({0},'{3}'
           ,'{7}'
           ,'{1}')", id, i.ZhiWuCengCi, i.ZhiWuShuXing, dt, "在任", dt, dt, i.OrgName);
                        if (!string.IsNullOrEmpty(sql1))
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);

                        if (i.AnZhiDanWei != 46 && i.AnZhiDanWei != 47)
                        {
                            string chasql = string.Format(@"INSERT INTO CheGai
           ([EmpID] ,[OldZhiJi] ,[Status] ,[Demo] ,[RelationID],KeyID,[type],CreateDate)
select {0},ed.dutylevel,'未导出','新增人员',{0},
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='开卡表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='开卡表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'开卡表',getdate()
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = {0}", id);
                            if (!string.IsNullOrEmpty(chasql))
                                AccessHelper.GetInstance().ExecuteNonQuery(chasql);
                        }
                    }
                }

            }

            return true;
        }
        #endregion
    }
}
